﻿#region << 文 件 说 明 >>
/*----------------------------------------------------------------
// 文件名称：VampirewalExportExcelService
// 创 建 者：杨程
// 创建时间：2022/2/11 9:56:59
// 文件版本：V1.0.0
// ===============================================================
// 功能描述：
//		
//
//----------------------------------------------------------------*/
#endregion

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;

namespace Vampirewal.Core.OperationExcelService
{
    /// <summary>
    /// Excel类型
    /// </summary>
    public enum ExcelType
    {

        xlsx = 0,
        xls = 1
    }
    /// <summary>
    /// Vampirewal操作Excel服务
    /// </summary>
    public sealed class VampirewalOperationExcelService : IVampirewalOperationExcelService
    {
        /// <summary>
        /// 执行导出
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="EntityList">导出数据源</param>
        /// <exception cref="Exception"></exception>
        public void ExportDataToExcel<T>(List<T> EntityList, string SaveFilePath, ExcelType excelType) where T : class, new()
        {
            try
            {
                if (EntityList == null)
                {
                    throw new Exception("导出Excel文件的数据源不能为空！");
                }

                Type type = typeof(T);

                if (string.IsNullOrEmpty(SaveFilePath))
                {
                    SaveFilePath = AppDomain.CurrentDomain.BaseDirectory;
                }
                //if (string.IsNullOrEmpty(SaveFileName))
                //{
                //    throw new Exception("导出Excel文件的文件名称不能为空！");
                //}

                var dt = ToDataTable(EntityList);

                switch (excelType)
                {
                    case ExcelType.xlsx:
                        var sfn = type.Name + ".xlsx";

                        SaveFilePath += sfn;
                        break;
                    case ExcelType.xls:
                        var sfn2 = type.Name + ".xlsx";

                        SaveFilePath += sfn2;
                        break;
                }

                //NPOI
                IWorkbook workbook;
                string FileExt = Path.GetExtension(SaveFilePath).ToLower();
                if (FileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (FileExt == ".xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }
                if (workbook == null)
                {
                    return;
                }
                //ISheet sheet = string.IsNullOrEmpty(SaveFileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(SaveFileName);
                ISheet sheet = workbook.CreateSheet(type.Name);


                //读取标题  
                IRow rowHeader = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = rowHeader.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

                //读取数据  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow rowData = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = rowData.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }

                //转为字节数组  
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件  
                using (FileStream fs = new FileStream(SaveFilePath, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                    fs.Close();
                }


            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {

            }


        }


        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        ///  <param name="sheetName">表名</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="FileFullPath">文件完整路径</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, string FileFullPath)
        {
            //if (string.IsNullOrEmpty(sheetName))
            //{
            //    throw new ArgumentNullException(sheetName);
            //}
            if (string.IsNullOrEmpty(FileFullPath))
            {
                throw new ArgumentNullException(FileFullPath);
            }
            var data = new DataTable();
            IWorkbook workbook = null;
            FileStream fs = null;
            try
            {
                fs = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read);
                if (FileFullPath.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (FileFullPath.IndexOf(".xls", StringComparison.Ordinal) > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }

                ISheet sheet = null;
                if (workbook != null)
                {
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
                }
                if (sheet == null) return data;
                var firstRow = sheet.GetRow(0);
                //一行最后一个cell的编号 即总的列数
                int cellCount = firstRow.LastCellNum;
                int startRow;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        var cellValue = cell.StringCellValue;
                        if (cellValue == null) continue;
                        var column = new DataColumn(cellValue);
                        data.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //最后一列的标号
                var rowCount = sheet.LastRowNum;
                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    //没有数据的行默认是null
                    if (row == null) continue;
                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        //同理，没有数据的单元格都默认是null
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }

                return data;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }


        /// <summary>
        /// 导入EXCEL文件转成List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="FileFullPath">文件完整路径</param>
        /// <returns></returns>
        public List<T> ExcelToList<T>(bool isFirstRowColumn, string FileFullPath)
        {
            Type type = typeof(T);

            return DataTableToList<T>(ExcelToDataTable(type.Name, isFirstRowColumn, FileFullPath));
        }

        /// <summary>
        /// List转DataTable
        /// <para>仅适用于导出excel的数据，且对应实体类的属性上有<paramref name="ExportExcelAttribute"/>特性</para>
        /// </summary>
        private DataTable ToDataTable<T>(List<T> items)
        {
            var tb = new DataTable(typeof(T).Name);

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            var clone = props.Clone();
            var cur = new List<PropertyInfo>();

            foreach (var item in clone as PropertyInfo[])
            {
                var export = item.GetCustomAttribute<ExportExcelAttribute>();

                if (export != null && export.IsCanExport)
                {
                    tb.Columns.Add(export.PropertyChineseName, item.PropertyType);
                    cur.Add(item);
                }
            }

            foreach (T item in items)
            {
                var values = new object[cur.Count];

                for (int i = 0; i < cur.Count; i++)
                {
                    values[i] = cur[i].GetValue(item, null);
                }

                tb.Rows.Add(values);
            }

            return tb;
        }

        /// <summary>
        /// DataTable转List
        /// <para>仅适用于excel导入的数据，且对应实体类的属性上有<paramref name="ExportExcelAttribute"/>特性</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private List<T> DataTableToList<T>(DataTable dt)
        {
            var list = new List<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                if (item.ItemArray[0] is DBNull)
                    continue;

                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    //PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);

                    PropertyInfo info = null;

                    foreach (var Prop in plist)
                    {
                        var customAttributes = Prop.GetCustomAttributes(typeof(ExportExcelAttribute), false).ToList();

                        foreach (var attribute in customAttributes)
                        {
                            if (attribute is ExportExcelAttribute export)
                            {
                                if (export.PropertyChineseName == dt.Columns[i].ColumnName)
                                {
                                    info = Prop;
                                    continue;
                                }
                            }
                        }

                        if (info != null)
                        {
                            continue;
                        }
                    }

                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else if (info.PropertyType.IsEnum)
                                {
                                    //int enumInt = Convert.ToInt32(item[i]);

                                    v = Enum.Parse(info.PropertyType, item[i].ToString());
                                }
                                else
                                {
                                    v = Convert.ChangeType(item[i], info.PropertyType);
                                }

                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }
    }
}
